# app/routes.py
from flask import render_template, flash, redirect, url_for, request
from flask_login import login_user, logout_user, login_required, current_user
from werkzeug.urls import url_parse
from datetime import datetime

from app import app, db
from app.models import User, Student, Course, Score
from app.forms import (
    LoginForm, RegisterForm, StudentForm, 
    CourseForm, ScoreForm, ProfileForm
)

# 首页
@app.route('/')
@login_required
def index():
    return render_template('index.html', title='首页')

# 登录
@app.route('/login', methods=['GET', 'POST'])
def login():
    if current_user.is_authenticated:
        return redirect(url_for('index'))
    form = LoginForm()
    if form.validate_on_submit():
        user = User.query.filter_by(username=form.username.data).first()
        if user is None or not user.check_password(form.password.data):
            flash('用户名或密码不正确', 'error')
            return redirect(url_for('login'))
        login_user(user, remember=form.remember_me.data)
        next_page = request.args.get('next')
        if not next_page or url_parse(next_page).netloc != '':
            next_page = url_for('index')
        return redirect(next_page)
    return render_template('login.html', title='登录', form=form)

# 注销
@app.route('/logout')
@login_required
def logout():
    logout_user()
    return redirect(url_for('login'))

# 注册
@app.route('/register', methods=['GET', 'POST'])
def register():
    if current_user.is_authenticated:
        return redirect(url_for('index'))
    form = RegisterForm()
    if form.validate_on_submit():
        user = User(
            username=form.username.data,
            email=form.email.data,
            role='student'  # 默认注册为学生
        )
        user.set_password(form.password.data)
        db.session.add(user)
        db.session.commit()
        flash('注册成功，请登录', 'success')
        return redirect(url_for('login'))
    return render_template('register.html', title='注册', form=form)

# 用户个人资料
@app.route('/profile', methods=['GET', 'POST'])
@login_required
def profile():
    form = ProfileForm()
    if form.validate_on_submit():
        current_user.username = form.username.data
        current_user.email = form.email.data
        if form.password.data:
            current_user.set_password(form.password.data)
        db.session.commit()
        flash('个人资料已更新', 'success')
        return redirect(url_for('profile'))
    elif request.method == 'GET':
        form.username.data = current_user.username
        form.email.data = current_user.email
    return render_template('profile.html', title='个人资料', form=form)

# ======================
# 学生管理
# ======================

# 学生列表
@app.route('/students')
@login_required
def students():
    page = request.args.get('page', 1, type=int)
    query = request.args.get('query', '')
    
    if query:
        students = Student.query.filter(
            Student.student_id.contains(query) |
            Student.name.contains(query) |
            Student.class_name.contains(query)
        ).order_by(Student.id.asc()).paginate(
            page=page, per_page=10, error_out=False
        )
    else:
        students = Student.query.order_by(Student.id.asc()).paginate(
            page=page, per_page=10, error_out=False
        )
    
    return render_template('students/list.html', title='学生管理', students=students, query=query)

# 添加学生
@app.route('/students/add', methods=['GET', 'POST'])
@login_required
def add_student():
    if current_user.role not in ['admin', 'teacher']:
        flash('权限不足', 'error')
        return redirect(url_for('students'))
    
    form = StudentForm()
    if form.validate_on_submit():
        # 检查学号唯一性
        if Student.query.filter_by(student_id=form.student_id.data).first():
            flash('学号已存在', 'error')
            return render_template('students/add.html', title='添加学生', form=form)
        
        student = Student(
            student_id=form.student_id.data,
            name=form.name.data,
            gender=form.gender.data,
            birthdate=form.birthdate.data,
            class_name=form.class_name.data
        )
        
        db.session.add(student)
        db.session.commit()
        flash('学生添加成功', 'success')
        return redirect(url_for('students'))
    
    return render_template('students/add.html', title='添加学生', form=form)

# 编辑学生
@app.route('/students/edit/<int:id>', methods=['GET', 'POST'])
@login_required
def edit_student(id):
    if current_user.role not in ['admin', 'teacher']:
        flash('权限不足', 'error')
        return redirect(url_for('students'))
    
    student = Student.query.get_or_404(id)
    form = StudentForm(obj=student)
    
    if form.validate_on_submit():
        # 检查学号唯一性（排除当前学生）
        existing = Student.query.filter(
            Student.student_id == form.student_id.data,
            Student.id != id
        ).first()
        
        if existing:
            flash('学号已存在', 'error')
            return render_template('students/add.html', title='编辑学生', form=form)
        
        student.student_id = form.student_id.data
        student.name = form.name.data
        student.gender = form.gender.data
        student.birthdate = form.birthdate.data
        student.class_name = form.class_name.data
        
        db.session.commit()
        flash('学生信息更新成功', 'success')
        return redirect(url_for('students'))
    
    return render_template('students/add.html', title='编辑学生', form=form, is_edit=True)

# 删除学生
@app.route('/students/delete/<int:id>', methods=['POST'])
@login_required
def delete_student(id):
    if current_user.role != 'admin':
        flash('权限不足', 'error')
        return redirect(url_for('students'))
    
    student = Student.query.get_or_404(id)
    
    # 先删除关联的成绩记录
    Score.query.filter_by(student_id=student.id).delete()
    
    db.session.delete(student)
    db.session.commit()
    
    flash('学生信息已删除', 'success')
    return redirect(url_for('students'))

# ======================
# 课程管理
# ======================

# 课程列表
@app.route('/courses')
@login_required
def courses():
    page = request.args.get('page', 1, type=int)
    query = request.args.get('query', '')
    
    if query:
        courses = Course.query.filter(
            Course.course_id.contains(query) |
            Course.name.contains(query)
        ).order_by(Course.id.asc()).paginate(
            page=page, per_page=10, error_out=False
        )
    else:
        courses = Course.query.order_by(Course.id.asc()).paginate(
            page=page, per_page=10, error_out=False
        )
    
    return render_template('courses/list.html', title='课程管理', courses=courses, query=query)

# 添加课程
@app.route('/courses/add', methods=['GET', 'POST'])
@login_required
def add_course():
    if current_user.role not in ['admin', 'teacher']:
        flash('权限不足', 'error')
        return redirect(url_for('courses'))
    
    form = CourseForm()
    if form.validate_on_submit():
        # 检查课程编号唯一性
        if Course.query.filter_by(course_id=form.course_id.data).first():
            flash('课程编号已存在', 'error')
            return render_template('courses/add.html', title='添加课程', form=form)
        
        course = Course(
            course_id=form.course_id.data,
            name=form.name.data,
            credit=form.credit.data,
            teacher=form.teacher.data
        )
        
        db.session.add(course)
        db.session.commit()
        flash('课程添加成功', 'success')
        return redirect(url_for('courses'))
    
    return render_template('courses/add.html', title='添加课程', form=form)

# 编辑课程
@app.route('/courses/edit/<int:id>', methods=['GET', 'POST'])
@login_required
def edit_course(id):
    if current_user.role not in ['admin', 'teacher']:
        flash('权限不足', 'error')
        return redirect(url_for('courses'))
    
    course = Course.query.get_or_404(id)
    form = CourseForm(obj=course)
    
    if form.validate_on_submit():
        # 检查课程编号唯一性（排除当前课程）
        existing = Course.query.filter(
            Course.course_id == form.course_id.data,
            Course.id != id
        ).first()
        
        if existing:
            flash('课程编号已存在', 'error')
            return render_template('courses/add.html', title='编辑课程', form=form)
        
        course.course_id = form.course_id.data
        course.name = form.name.data
        course.credit = form.credit.data
        course.teacher = form.teacher.data
        
        db.session.commit()
        flash('课程信息更新成功', 'success')
        return redirect(url_for('courses'))
    
    return render_template('courses/add.html', title='编辑课程', form=form, is_edit=True)

# 删除课程
@app.route('/courses/delete/<int:id>', methods=['POST'])
@login_required
def delete_course(id):
    if current_user.role != 'admin':
        flash('权限不足', 'error')
        return redirect(url_for('courses'))
    
    course = Course.query.get_or_404(id)
    
    # 先删除关联的成绩记录
    Score.query.filter_by(course_id=course.id).delete()
    
    db.session.delete(course)
    db.session.commit()
    
    flash('课程已删除', 'success')
    return redirect(url_for('courses'))

# ======================
# 成绩管理
# ======================

# 成绩列表
@app.route('/scores')
@login_required
def scores():
    page = request.args.get('page', 1, type=int)
    student_id = request.args.get('student_id', type=int)
    course_id = request.args.get('course_id', type=int)
    
    # 查询条件
    query = Score.query.join(Student, Score.student_id == Student.id)\
                      .join(Course, Score.course_id == Course.id)
    
    if student_id:
        query = query.filter(Score.student_id == student_id)
    
    if course_id:
        query = query.filter(Score.course_id == course_id)
    
    scores = query.order_by(Score.id.asc()).paginate(
        page=page, per_page=10, error_out=False
    )
    
    # 获取学生和课程选项
    students = Student.query.all()
    courses = Course.query.all()
    
    return render_template('scores/list.html', title='成绩管理', 
                          scores=scores, students=students, courses=courses,
                          selected_student=student_id, selected_course=course_id)

# 添加成绩
@app.route('/scores/add', methods=['GET', 'POST'])
@login_required
def add_score():
    if current_user.role not in ['admin', 'teacher']:
        flash('权限不足', 'error')
        return redirect(url_for('scores'))
    
    form = ScoreForm()
    
    # 动态设置学生和课程选项
    form.student_id.choices = [(s.id, f"{s.student_id} - {s.name}") for s in Student.query.all()]
    form.course_id.choices = [(c.id, f"{c.course_id} - {c.name}") for c in Course.query.all()]
    
    if form.validate_on_submit():
        # 检查是否已存在该学生该课程的成绩
        existing = Score.query.filter_by(
            student_id=form.student_id.data,
            course_id=form.course_id.data
        ).first()
        
        if existing:
            flash('该学生该课程已有成绩记录', 'error')
            return render_template('scores/add.html', title='添加成绩', form=form)
        
        score = Score(
            student_id=form.student_id.data,
            course_id=form.course_id.data,
            score=form.score.data,
            remark=form.remark.data
        )
        
        db.session.add(score)
        db.session.commit()
        flash('成绩添加成功', 'success')
        return redirect(url_for('scores'))
    
    return render_template('scores/add.html', title='添加成绩', form=form)

# 编辑成绩
@app.route('/scores/edit/<int:id>', methods=['GET', 'POST'])
@login_required
def edit_score(id):
    if current_user.role not in ['admin', 'teacher']:
        flash('权限不足', 'error')
        return redirect(url_for('scores'))
    
    score = Score.query.get_or_404(id)
    form = ScoreForm(obj=score)
    
    # 动态设置学生和课程选项
    form.student_id.choices = [(s.id, f"{s.student_id} - {s.name}") for s in Student.query.all()]
    form.course_id.choices = [(c.id, f"{c.course_id} - {c.name}") for c in Course.query.all()]
    
    if form.validate_on_submit():
        score.student_id = form.student_id.data
        score.course_id = form.course_id.data
        score.score = form.score.data
        score.remark = form.remark.data
        
        db.session.commit()
        flash('成绩更新成功', 'success')
        return redirect(url_for('scores'))
    
    return render_template('scores/add.html', title='编辑成绩', form=form, is_edit=True)

# 删除成绩
@app.route('/scores/delete/<int:id>', methods=['POST'])
@login_required
def delete_score(id):
    if current_user.role not in ['admin', 'teacher']:
        flash('权限不足', 'error')
        return redirect(url_for('scores'))
    
    score = Score.query.get_or_404(id)
    db.session.delete(score)
    db.session.commit()
    
    flash('成绩记录已删除', 'success')
    return redirect(url_for('scores'))

# ======================
# 统计分析
# ======================

@app.route('/statistics')
@login_required
def statistics():
    # 获取课程平均分
    course_stats = db.session.query(
        Course.name,
        db.func.avg(Score.score).label('avg_score'),
        db.func.min(Score.score).label('min_score'),
        db.func.max(Score.score).label('max_score'),
        db.func.count(Score.id).label('student_count')
    ).outerjoin(Score, Course.id == Score.course_id)\
     .group_by(Course.id)\
     .all()
    
    # 获取学生总平均分
    student_stats = db.session.query(
        Student.name,
        db.func.avg(Score.score).label('avg_score'),
        db.func.count(Score.id).label('course_count')
    ).outerjoin(Score, Student.id == Score.student_id)\
     .group_by(Student.id)\
     .all()
    
    return render_template('statistics.html', title='成绩统计', 
                          course_stats=course_stats, student_stats=student_stats)

# 学生成绩详情
@app.route('/students/<int:id>/scores')
@login_required
def student_scores(id):
    student = Student.query.get_or_404(id)
    scores = Score.query.join(Course, Score.course_id == Course.id)\
                       .filter(Score.student_id == id)\
                       .order_by(Course.name).all()
    
    # 计算平均分
    total_score = sum(s.score for s in scores)
    avg_score = total_score / len(scores) if scores else 0
    
    return render_template('students/scores.html', title=f'{student.name}的成绩', 
                          student=student, scores=scores, avg_score=avg_score)

# 课程成绩详情
@app.route('/courses/<int:id>/scores')
@login_required
def course_scores(id):
    course = Course.query.get_or_404(id)
    scores = Score.query.join(Student, Score.student_id == Student.id)\
                       .filter(Score.course_id == id)\
                       .order_by(Student.name).all()
    
    # 计算平均分、最高分、最低分
    if scores:
        avg_score = sum(s.score for s in scores) / len(scores)
        max_score = max(s.score for s in scores)
        min_score = min(s.score for s in scores)
    else:
        avg_score = max_score = min_score = 0
    
    return render_template('courses/scores.html', title=f'{course.name}的成绩', 
                          course=course, scores=scores, avg_score=avg_score,
                          max_score=max_score, min_score=min_score)